/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Entiti.Member;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author mini
 */
public class controllerMember {

    private Connection conn=null;
    Member member = new Member();
    Statement stmt;
    ResultSet rset;
    String query;
    String sqlString;

    public void hapusMember(int id) throws SQLException {
        query = "delete from member where id ='" + id + "'";
        DatabaseHandler.update(query);
    }

    public ResultSet getAllMember() throws SQLException {
        query = "SELECT * from member";
        rset = DatabaseHandler.execute(query);
        return rset;
    }

    public boolean tambahMember(int id, String nama, String noHp, String alamat, String email) {
        try {
            String queryInsert = "INSERT INTO member (id, namaMember, noHpMember, alamatMember, alamatEmail) values(" + id + ",'" + nama + "', '" + noHp + "','" + alamat + "','" + email + "')";
            DatabaseHandler.update(queryInsert);
            return true;
        } catch (SQLException ex) {
            return false;
        }

    }

    public int getJumlahMember() throws SQLException {
        int jumlahMember = 0;

        String queryJumlah = "select count(*) from member";
        ResultSet result2 = DatabaseHandler.execute(queryJumlah);
        if (result2.next()) {
            jumlahMember = Integer.parseInt(result2.getString(1));
        }
        return jumlahMember;

    }

    public static boolean updateMember(String id, String nama, String noHp, String alamat, String email) {
        try {
            String query = "update member set namamember='" + nama + "', alamatmember='" + alamat + "', nohpmember='" + noHp + "', alamatemail='" + email + "' where id='" + id + "'";
            DatabaseHandler.update(query);
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(controllerMember.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    //* tambahan method update, bisa jadi pengganti method yg diataas, haha
    public Member updateDataMember(Member member) throws SQLException
    {
        PreparedStatement prepare=null;

        String sql="update penjualanpulsa set alamatemail=(?), nohpmember=(?),"
                + "alamatmember=(?) where namamember=(?)";
        prepare=conn.prepareStatement(sql);

        prepare.setString(1, member.getAlamatEmail());
        prepare.setString(2, member.getNoHp());
        prepare.setString(3, member.getAlamatMember());
        prepare.setString(4, member.getNamaMember());
        prepare.execute();
        prepare.close();
        conn.commit();
        return member;
    }

    public static void main(String[] args) throws SQLException {
        DatabaseHandler conn = new DatabaseHandler();
        try {
            String query = "select * from member";
            @SuppressWarnings("static-access")
            Statement statement = conn.getDBConnection().createStatement();
            ResultSet result = statement.executeQuery(query);

            while (result.next()) {
                System.out.println("Nama : " + result.getString("namaMember") + " | ");
                System.out.println("ID : " + result.getString("idMember") + " | ");
                System.out.println("Alamat : " + result.getString("alamatMember") + " | ");
                System.out.println("No HP : " + result.getString("noHpMember") + " |");
                System.out.println("Email : " + result.getString("alamatEmail") + "");
            }
            statement.close();
        } catch ( Exception ex) {
            System.out.println("message: " + ex.getMessage());
        }

    }

    /* scrip cari member mereferensi source code buatan Arda dan Tinus dari aplikasi perpustakaan
     * yang dilombakan di ELINFO 2011 */
    public static void cariMember(JTable table, JTextField jt) {
        try {
            String key = "%" + jt.getText().trim() + "%";
            String query = "select * from member where lower(namamember) like (?) " + "or lower(alamatemail) like (?) " + "or lower(alamatmember)like (?) " + "or lower(nohpmember) like (?) order by id";
            PreparedStatement psmt = DatabaseHandler.getDBConnection().prepareStatement(query);
            psmt.setObject(1, key);
            psmt.setObject(2, key);
            psmt.setObject(3, key);
            psmt.setObject(4, key);

            ResultSet rs = psmt.executeQuery();
            LinkedList x = new LinkedList();
            while (rs.next()) {
                x.add(rs.getObject("id"));
                x.add(rs.getObject("namamember"));
                x.add(rs.getObject("nohpmember"));
                x.add(rs.getObject("alamatemail"));
                x.add(rs.getObject("alamatmember"));

            }

            DefaultTableModel df = new DefaultTableModel();
            String[] identifier = {"ID","Nama", "No Hp", "Email", "Alamat"};
            df.setColumnIdentifiers(identifier);
            while (!x.isEmpty()) {
                String[] data = new String[5];
                for (int i = 0; i < data.length; i++) {
                    data[i] = String.valueOf(x.remove());
                }
                df.addRow(data);
            }
            table.setModel(df);
            psmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(controllerMember.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
     public int autoGenerateID() {
        int id = 0;
     
        try {

            String query2 = "select to_char(max(id)+1) from member";
     
            @SuppressWarnings("static-access")
            ResultSet rset2 = DatabaseHandler.execute(query2);
            if(rset2.next()){
            id = Integer.parseInt(rset2.getString(1));
            }
            System.out.println(id);
            return id;
        } catch ( SQLException ex) {
            Logger.getLogger(controllerMember.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(id);
            return id;
        }
        
    }
}
